﻿
alter PROC PROC_GetRuleCode
---获取编号-------
@RuleId nvarchar(50),
@SerialNo nvarchar(50),
@UserId nvarchar(50),
@InitCount int=0,
@NewMaxCount int output
as
   set  @NewMaxCount=0
	declare @MaxCount int
	declare @NewCount int =@InitCount
	declare @SerialRuleNumberId nvarchar(50)
	set @MaxCount=0
	declare @error int
	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
begin tran  
select  @MaxCount=MaxCount,@SerialRuleNumberId=SerialRuleNumberId from COMM_SerialRuleNumber with(updlock) where  SerialRuleId=@RuleId and SerialRuleNo=@RuleId+@SerialNo
if @MaxCount>0
	begin
		set @NewCount=@MaxCount
	end
if @NewCount<=0 
	begin
		set @NewCount=1
	end 
else 
	set @NewCount+=1
if @MaxCount<=0
	begin
	insert into COMM_SerialRuleNumber(
	[SerialRuleNumberId],
	[SerialRuleNo],
	[SerialRuleId],
	[MaxCount],
	[CreateBy],
	[CreateDate],
	[LastModifyUserId],
	[LastModifyDate],
	[IsDeleted]
	)
	values(newid(),@RuleId+@SerialNo, @RuleId,@NewCount,@UserId,getdate(),@UserId,getdate(),0)
	set @error=@@ERROR
	end
	else
	begin
	update COMM_SerialRuleNumber set [MaxCount]=@NewCount,[LastModifyUserId]=@UserId,[LastModifyDate]=GETDATE() 
	 where SerialRuleId=@RuleId and SerialRuleNumberId=@SerialRuleNumberId
	 set @error=@@ERROR
	end
	if @error>0
	begin 
		rollback tran
		select @NewMaxCount=-1
		end
	else
		begin
			commit tran 
			select @NewMaxCount=@NewCount
		end
		

GO


/*******Mysql********/
CREATE PROCEDURE PROC_GetRuleCode (
	IN RuleId VARCHAR (50),
	IN SerialNo VARCHAR (50),
	IN UserId VARCHAR (50),
	IN InitCount INT,
	OUT NewMaxCount INT
)
BEGIN


DECLARE
	NewCount INT;

DECLARE
	ErrorCount INT;

set
	@pSerialRuleNumberId='';

	set
		@pMaxCount=0;



START TRANSACTION;

SELECT
	@pMaxCount:= MaxCount,
	@pSerialRuleNumberId:= SerialRuleNumberId
FROM
	COMM_SerialRuleNumber
WHERE
	SerialRuleId = RuleId
AND SerialRuleNo = RuleId + SerialNo;


IF @pMaxCount > 0 THEN

SET NewCount = @pMaxCount;


END
IF;


IF NewCount <= 0 THEN

SET NewCount = 1;


ELSE

SET NewCount = NewCount+1;


END
IF;


IF @pMaxCount <= 0 THEN
	INSERT INTO COMM_SerialRuleNumber (
		SerialRuleNumberId,
		SerialRuleNo,
		SerialRuleId,
		MaxCount,
		CreateBy,
		CreateDate,
		LastModifyUserId,
		LastModifyDate,
		IsDeleted
	)
VALUES
	(
		UUID(),
		RuleId + SerialNo,
		RuleId,
		NewCount,
		UserId,
		NOW(),
		UserId,
		NOW(),
		0
	);

ELSE
	
		UPDATE COMM_SerialRuleNumber
	SET MaxCount= NewCount ,LastModifyUserId= UserId ,LastModifyDate= NOW()
	WHERE
		SerialRuleId = RuleId
	AND SerialRuleNumberId = @pSerialRuleNumberId;


END IF;
IF ErrorCount > 0
then
	ROLLBACK;

set
	NewMaxCount=- 1;


ELSE

	COMMIT;

set 
	NewMaxCount=NewCount;


END IF;
END